const connection = require('../app/database')
class UserService {
  async createUser(username, password,roleId='',realname='',telephone='') {
    const statement = `
      INSERT INTO users (username,password,role_id,realname,telephone) VALUES (?,?,?,?,?);
    `
    const result = await connection.execute(statement, [username, password,roleId,realname,telephone])
    return result
  }
  async getUserByUsername(username) {
    const statement = `
      SELECT * FROM users WHERE username = ? ;
    `
    const [result] = await connection.execute(statement, [username])
    return result
  }
  async getUserList(pagenum = '1',pagesize = '10',id='',username='',realname='',telephone='') {
    const statement = `		
      SELECT (SELECT COUNT(*) FROM users) totalCount,
      JSON_ARRAYAGG(JSON_OBJECT('id',u.user_id,'username',u.username,'telephone',u.telephone,'realname',u.realname,'roleId',u.role_id)) list
      FROM	(SELECT * FROM users	
      WHERE user_id LIKE '%${id}%' AND username LIKE '%${username}%' AND telephone LIKE '%${telephone}%' AND realname LIKE '%${realname}%'
      LIMIT ?,?) u;	
    `
    const offset = (pagenum - 1) * pagesize
    const [result] = await connection.execute(statement, [offset+'', pagesize])
    return result[0]
   
  }
  async getUserById(id) {
    const statement = `
      SELECT user_id userId,username,role_id roleId FROM users WHERE user_id = ?;
    `
    const [result] = await connection.execute(statement,[id])
    return result[0]
  }
  async updateUserById(id,username,realname,roleId,telephone) {
  
    const statement = `
      UPDATE users SET username = ?,realname = ?,role_id = ?,telephone = ?
      WHERE user_id = ?;
    `
    const [result] = await connection.execute(statement, [username,realname,roleId,telephone,id])
    return result
  }
  async removeUserById(id) {
    const statement = `
      DELETE FROM users WHERE user_id = ?;
    `
    
    try {
      const [res] = await connection.execute(statement, [id])
      return res
    } catch (error) {
      console.log(error);
    }

  }
  async changeUserRole(id, rid) {
    const statement = `
      UPDATE users SET role_id = ? WHERE user_id = ?;
    `
    const [result] = await connection.execute(statement,[rid,id])

    return result
  }
}
module.exports = new UserService()